package com.zjweu.hkg.DAO;

import com.zjweu.hkg.DTO.ArticleWithTagsDTO;
import com.zjweu.hkg.Handler.StringToListTypeHandler;
import com.zjweu.hkg.POJO.*;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public interface ArticleDAO {
    // 插入文章
    @Insert("INSERT INTO Article (articleTitle, articleContent, authorUID, createDate, updateDate,readTime,articleUUID,permission,status) " +
            "VALUES (#{articleTitle}, #{articleContent}, #{authorUID}, now(), now(),0,#{articleUUID},#{permission},0)")
    @Options(useGeneratedKeys = true, keyProperty = "articleId") // 自动获取生成的 articleId
    void insertArticle(Article article);

    // 插入标签（如果标签不存在）
    @Insert("<script>" +
            "INSERT INTO Tag (tagContent) " +
            "SELECT #{tagContent} WHERE NOT EXISTS (SELECT 1 FROM Tag WHERE tagContent = #{tagContent})" +
            "</script>")
    void insertTagIfNotExist(Tag tag);

    // 获取标签ID
    @Select("SELECT tagId FROM Tag WHERE tagContent = #{tagContent}")
    int getTagIdByContent(String tagContent);

    // 插入文章和标签的关联关系
    @Insert("INSERT INTO ArticleTags (articleId, tagId) VALUES (#{articleId}, #{tagId})")
    void insertArticleTag(ArticleTags articleTags);
    //根据文章id查找tags
    @Select("select * from ArticleTags where articleId=#{articleId}")
    List<ArticleTags> findArticleTagsByArticleId(int articleId);

    @Select("select * from Tag where tagId=#{tagId}")
    Tag findTagsByTagId(int tagId);
    @Select("select * from Article where status=0")
    List<Article> findAllArticle();
    @Select("select * from Article where articleId=#{articleId} and status=0")
    Article findArticleByArticleId(int articleId);
    @Select("select * from Article where authorUID=#{userId} and status=0")
    List<Article> findArticleByUserId(int userId);
    @Update("update Article set readTime=#{newReadTime} where articleId=#{articleId}")
    int addReadTimeByArticleId(int articleId,int newReadTime);
    @Insert("insert into ArticleViewHistory (articleId,userId,createTime) values (#{articleId},#{userId},now())" )
    int addArticleViewHistory(int articleId, int userId);

    @Select("select * from ArticleViewHistory where userId=#{userId}")
    List<ArticleViewHistory> findArticleViewHistoryByUserId(int userId);

    @Select("SELECT createTime\n" +
            "FROM ArticleViewHistory\n" +
            "WHERE userId = #{userId} AND articleId = #{articleId}\n" +
            "ORDER BY createTime DESC\n" +
            "LIMIT 1;\n")
    ArticleViewHistory findLastViewTimeByUserIdAndArticleId(int userId, int articleId);

    //更新文章
    @Update("update Article set articleTitle=#{articleTitle},articleContent=#{articleContent},permission=#{permission},status=#{status},updateDate=now() where articleId=#{articleId}")
    //自动返回更新的articleId
    @Options(useGeneratedKeys = true, keyProperty = "articleId")
    int updateArticle(Article article);

    //根据文章id删除所以Tag
    @Delete("delete from ArticleTags where articleId=#{articleId}")
    int deleteArticleTagsByArticleId(int articleId);
    @Update("update Article set status=1 where articleId=#{articleId}")
    int deleteArticleByArticleId(int articleId);
    @Insert("insert into ArticleFiles (fileName,articleUUID,createTime) values (#{fileName},#{articleUUID},now())")
    int insertArticleFiles(ArticleFiles articleFiles);

    @Select({
            "<script>",
            "SELECT ",
            "  a.articleId, a.articleTitle, a.articleContent, a.authorUID,",
            "  a.createDate, a.updateDate, a.readTime, a.articleUUID, a.permission,",
            "  u.userName AS authorName,",
            "  GROUP_CONCAT(DISTINCT t.tagContent ORDER BY t.tagContent) AS tagNames,",
            "  (",
            "<foreach item='tag' collection='tags' separator=' + '>",
            "    COUNT(DISTINCT CASE WHEN t.tagContent LIKE CONCAT('%', #{tag}, '%') THEN t.tagId END)",
            "</foreach>",
            "  ) AS matchCount ",
            "FROM Article a ",
            "JOIN User u ON a.authorUID = u.id ",
            "JOIN ArticleTags at ON a.articleId = at.articleId ",
            "JOIN Tag t ON at.tagId = t.tagId ",
            "WHERE ",
            "<foreach item='tag' collection='tags' separator=' OR '>",
            "  t.tagContent LIKE CONCAT('%', #{tag}, '%')",
            "</foreach> and a.status=0",
            "GROUP BY a.articleId, u.userName ",
            "ORDER BY matchCount DESC, a.createDate DESC",
            "</script>"
    })
    @Results({
            @Result(property = "article.articleId", column = "articleId"),
            @Result(property = "article.articleTitle", column = "articleTitle"),
            @Result(property = "article.articleContent", column = "articleContent"),
            @Result(property = "article.authorUID", column = "authorUID"),
            @Result(property = "author.userName", column = "authorName"),
            @Result(property = "article.createDate", column = "createDate"),
            @Result(property = "article.updateDate", column = "updateDate"),
            @Result(property = "article.readTime", column = "readTime"),
            @Result(property = "article.articleUUID", column = "articleUUID"),
            @Result(property = "article.permission", column = "permission"),
            @Result(property = "tagNames", column = "tagNames",
                    javaType = List.class, jdbcType = JdbcType.VARCHAR,
                    typeHandler = StringToListTypeHandler.class)
    })
    List<ArticleWithTagsDTO> getArticlesByTags(@Param("tags") List<String> tags);

    // 获取单个文章标签的辅助查询（用于替代GROUP_CONCAT方案）
    @Select("SELECT t.tagContent FROM ArticleTags at " +
            "JOIN Tag t ON at.tagId = t.tagId " +
            "WHERE at.articleId = #{articleId}")
    List<String> getTagsByArticleId(int articleId);

    @Select({
            "<script>",
            "SELECT a.* FROM Article a WHERE a.articleId IN (",
            "  SELECT at.articleId FROM ArticleTags at",
            "  JOIN Tag t ON at.tagId = t.tagId",
            "  WHERE ",
            "<foreach item='tag' collection='tags' separator=' OR '>",
            "    t.tagContent LIKE CONCAT('%', #{tag}, '%')",
            "</foreach>",
            ")",
            "</script>"
    })
    List<ArticleWithTagsDTO> simpleTest(@Param("tags") List<String> tags);

    @Select({
            "<script>",
            "SELECT ",
            "    a.articleId, ",
            "    a.articleTitle, ",
            "    a.articleContent, ",
            "    a.authorUID,",
            "    u.userName AS authorName,",
            "    a.createDate, ",
            "    a.updateDate, ",
            "    a.readTime, ",
            "    a.articleUUID, ",
            "    a.permission,",
            "    GROUP_CONCAT(t.tagContent) as tagNames ",
            "FROM ",
            "    Article a",
            "LEFT JOIN ",
            "    ArticleTags at ON a.articleId = at.articleId",
            "LEFT JOIN ",
            "    Tag t ON at.tagId = t.tagId",
            "LEFT JOIN ",
            "    User u ON a.authorUID = u.id",
            "WHERE ",
            "    a.articleTitle LIKE CONCAT('%', #{articleTitle}, '%') and a.status=0 and a.permission=2",
            "GROUP BY ",
            "    a.articleId, ",
            "    u.userName",
            "</script>"
    })
    @Results({
            @Result(property = "article.articleId", column = "articleId"),
            @Result(property = "article.articleTitle", column = "articleTitle"),
            @Result(property = "article.articleContent", column = "articleContent"),
            @Result(property = "article.authorUID", column = "authorUID"),
            @Result(property = "author.userName", column = "authorName"),
            @Result(property = "article.createDate", column = "createDate"),
            @Result(property = "article.updateDate", column = "updateDate"),
            @Result(property = "article.readTime", column = "readTime"),
            @Result(property = "article.articleUUID", column = "articleUUID"),
            @Result(property = "article.permission", column = "permission"),
            @Result(property = "tagNames", column = "tagNames",
                    javaType = List.class, jdbcType = JdbcType.VARCHAR,
                    typeHandler = StringToListTypeHandler.class)
    })
    List<ArticleWithTagsDTO> likeSearchArticleByName(String articleTitle);

    //根据userId查询用户关注的博主，获取这些博主的文章
   @Select("select a.* from Article a JOIN Follow f on a.authorUID=f.followedId where f.followerId=#{userId} and f.status=0 and a.status=0 and a.permission=2")
   @Results({
           @Result(property = "article.articleId", column = "articleId"),
           @Result(property = "article.articleTitle", column = "articleTitle"),
           @Result(property = "article.articleContent", column = "articleContent"),
           @Result(property = "article.authorUID", column = "authorUID"),
           @Result(property = "author.userName", column = "authorName"),
           @Result(property = "article.createDate", column = "createDate"),
           @Result(property = "article.updateDate", column = "updateDate"),
           @Result(property = "article.readTime", column = "readTime"),
           @Result(property = "article.articleUUID", column = "articleUUID"),
           @Result(property = "article.permission", column = "permission"),
           @Result(property = "tagNames", column = "tagNames",
                   javaType = List.class, jdbcType = JdbcType.VARCHAR,
                   typeHandler = StringToListTypeHandler.class)
   })

    List<ArticleWithTagsDTO> getFollowedArticles(int userId);
}

